package rohyunji;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class J03_memberDAO implements J03_ImemberDAO {

	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:rohji/rohji@112.216.253.125:1521:xe";
	private String insertSQL = "insert into J03MEMBER values(exam.nextval,?,?,?,?,sysdate)";
	private String selectSQL="select *from J03MEMBER order by mno desc";

	@Override
	public boolean insertMember(J03_memberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;

		try {

			conn = this.openDataBase();
			pstmt=conn.prepareStatement(insertSQL);
			// ? 매개변수에 대입할 값 지정
			
			pstmt.setString(1, m.getUserid());
			pstmt.setString(2, m.getUserpwd());
			pstmt.setString(3, m.getUsername());
			pstmt.setString(4, m.getUseremail());
			pstmt.executeUpdate();
			result = true;
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDataBase(conn, pstmt, null);
		}
		return result;
	}

	// JDBC Connection 객체를 생성하는 메서드

	private Connection openDataBase() {
		try {
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;

	}

	// JDBC 객체를 닫는 메서드
	private void closeDataBase(Connection c, PreparedStatement p, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
				rs = null;
			} catch (Exception e) {
			}
		}
		if (p != null) {
			try {
				p.close();
				p = null;
			} catch (Exception e) {
			}
		}
		if (c != null) {
			try {
				c.close();
				c = null;
			} catch (Exception e)
			{
				e.printStackTrace();
			}
		}

	}

	@Override
	//select 문으로전체 회원정보를 조회한후 MemberVo를 내용으로 가지는 동적배열 List로 결과를 넘김 
	public List<J03_memberVO> selectMember() {
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		List<J03_memberVO> result=new ArrayList<>();
		try
		{
			conn=openDataBase();
			pstmt=conn.prepareStatement(selectSQL);
			rs=pstmt.executeQuery();
			
			while(rs.next())
			{
				J03_memberVO m=new J03_memberVO();
				m.setMno(rs.getString("mno"));
				m.setUserid(rs.getString("userid"));
				m.setUserpwd(rs.getString("userpwd"));
				m.setUsername(rs.getString("username"));
				m.setUseremail(rs.getString("useremail"));
				m.setJoindate(rs.getString("joindate"));
				result.add(m);//배열에 담음 
			}
			
		}
		catch(Exception e){e.printStackTrace();} finally{closeDataBase(conn, pstmt, rs);}
		
		return result;
	}

}
